package cn.edu.my.index.service.impl;

import java.io.FileInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;
import java.util.Date;
import java.text.DecimalFormat;
import java.math.BigDecimal;

import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import cn.edu.my.index.service.ExcelReader;
import cn.edu.my.index.model.THSIndex;

public class ExcelReaderImpl implements ExcelReader{
	private static ExcelReaderImpl excelReader;
	private List<THSIndex> thsIndexList=new ArrayList<THSIndex>();
	
	private ExcelReaderImpl(){
	}
	
	public static ExcelReader getInstance(){
		if(null==excelReader){
			excelReader=new ExcelReaderImpl();	
		}
		return new ExcelReaderImpl();
	}

	public void method2() throws Exception {
	
	    InputStream is = new FileInputStream("D:\\同花顺指数_20140226.xls");
	    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
	
	    ExcelExtractor extractor = new ExcelExtractor(wb);
	    extractor.setIncludeSheetNames(false);
	    extractor.setFormulasNotResults(false);
	    extractor.setIncludeCellComments(true);
	
	    String text = extractor.getText();
	    //System.out.println(text);
	}

	public List<THSIndex> readExcel(String file) throws Exception {
	    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
	    HSSFSheet sheet = wb.getSheetAt(0);
	    DecimalFormat df = new DecimalFormat("#.00");//保留小数点后两位
	    
	    FileOutputStream stream = new FileOutputStream(new File(file+"1"));
	    wb.write(stream);
		stream.close();
		
		List<THSIndex> thsIndexList=new ArrayList<THSIndex>();
	    for(int i=0; i< sheet.getLastRowNum()+1; i++){
	    	if (i == 0) {
    			continue;
    		}
    		Row row = sheet.getRow(i);
		    THSIndex thsIndex=new THSIndex();
		    List temp=new ArrayList();
		    for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator(); iter2.hasNext();) {
				Cell cell = iter2.next();
			    if(Cell.CELL_TYPE_NUMERIC==cell.getCellType()){
			    	double content = cell.getNumericCellValue();// 除非是numeric类型，否则这样迭代读取会有错误
			      	temp.add(content);
			      	//System.out.println(content);	
			    }
			    if(Cell.CELL_TYPE_STRING==cell.getCellType()){
			    	/*if(cell.getStringCellValue().equals("--")){
						continue;
					}*/
			      	String content = cell.getStringCellValue();// 除非是sring类型，否则这样迭代读取会有错误
			       	temp.add(content);
			       	//System.out.println(content);	
			    }
		    }
		    
			thsIndex.setIndexDate(new Date());
			thsIndex.setSectionName((String)temp.get(0));
	    	thsIndex.setRiseExtent(new BigDecimal(df.format(Double.parseDouble(temp.get(1).toString())*100)));//由于使用百分比表示，所以最后还要乘以100
	    	thsIndex.setRiseSpeed(new BigDecimal(df.format(Double.parseDouble(temp.get(2).toString())*100)));//df.format方法表示保留小数点后两位
	    	thsIndex.setDdeNetAmount(new BigDecimal(df.format(Double.parseDouble(temp.get(3).toString()))));
	    	thsIndex.setDdeSum(new BigDecimal(temp.get(4).toString().substring(0,temp.get(4).toString().length()-2)));
	    	thsIndex.setAmountRatio(new BigDecimal(df.format(Double.parseDouble(temp.get(5).toString()))));
	    	thsIndex.setRiseNumber(new BigDecimal(temp.get(6).toString().substring(0,temp.get(6).toString().length()-2)));
	    	thsIndex.setDropNumber(new BigDecimal(temp.get(7).toString().substring(0,temp.get(7).toString().length()-2)));
	    	thsIndex.setLeadingRiseShare(temp.get(8).toString());
	    	thsIndex.setLastFivedayRiseExtent(new BigDecimal(df.format(Double.parseDouble(temp.get(9).toString())*100)));
	    	thsIndex.setLastTendayRiseExtent(new BigDecimal(df.format(Double.parseDouble(temp.get(10).toString())*100)));
	    	thsIndex.setLastTwentydayRiseExtent(new BigDecimal(df.format(Double.parseDouble(temp.get(11).toString())*100)));
	    	//System.out.println(new BigDecimal(temp.get(6).toString().replaceAll("0+?$","").replaceAll("[.]$","")));	    	
	    	
	    	if(temp.get(12).toString().contains("E")){
	    		String[] str=temp.get(12).toString().split("E");
	    		double d=Double.parseDouble(str[0])*10*Integer.parseInt(str[1]);
	    		
	    		thsIndex.setCirculatingMarketValue(new BigDecimal(String.valueOf(d)));
	    	}else{
	    		thsIndex.setCirculatingMarketValue(new BigDecimal(temp.get(12).toString()));
	    	}
	    	thsIndex.setTransactionVolume(new BigDecimal(temp.get(12).toString().substring(0,temp.get(12).toString().length()-2)));
	    	thsIndex.setTransactionSum(new BigDecimal(temp.get(13).toString().substring(0,temp.get(13).toString().length()-2)));
	    	thsIndex.setTotalMarketValue(new BigDecimal(temp.get(14).toString().substring(0,temp.get(14).toString().length()-2)));
	    	
	    	thsIndex.setCirculatingMarketValue(new BigDecimal(temp.get(15).toString().substring(0,temp.get(15).toString().length()-2)));
	    	thsIndexList.add(thsIndex);
	    }
	    return thsIndexList;
	}
	
	public void removeColumn(String file) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
	    HSSFSheet sheet = wb.getSheetAt(0);
	    FileOutputStream out=null;
	    
		try {
			for (Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.hasNext();) {
				HSSFRow row = (HSSFRow)rowIterator.next();
	        	HSSFCell cell = row.getCell(1);
	        	if(cell == null){
	        		continue;
	        	}
	        	row.removeCell(cell);
	        }
	        out=new FileOutputStream(file);
	        wb.write(out);
	    } catch (IOException e) {
        	 e.printStackTrace();
        } finally {
        	try {
            	out.close();
            } catch (IOException e) {
            	e.printStackTrace();
            }
        }
	}
}